import pymongo
from config.configs import ALPHA_DB_HOST, ALPHA_DB_PORT
from tools.funciton import spuId_to_articleNumber, skuId_to_size, write_xlsx, timestamp_switch_date

alpha = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).alpha


def repair_export():
    """修复订单导出"""
    li = []
    pipeline = [
        {
            '$match': {
                'status': "finished",
                'createdAt': {
                    '$gt': 1717171200000,
                    '$lt': 1719763200000
                }
            }
        },
        {
            '$unwind':
                {
                    'path': "$stockList"
                }
        },
        {
            '$project':
                {
                    'warehouseCode': 1,
                    'serial': 1,
                    'spu': "$stockList.spuId",
                    'sku': "$stockList.skuId",
                    'old_shelf': "$stockList.originalShelfCode",
                    'new_shelf': "$stockList.shelfCode",
                    'old_uni': "$stockList.originalUniqueCode",
                    'new_uni': "$stockList.uniqueCode",
                    'originalFailReason': '$stockList.originalFailReason',
                    'price': "$stockList.price",
                    'status': 1,
                    'finishedAt': 1,
                    'merchantCode': 1,
                    'rejectReason': "$stockList.rejectReason",
                }
        },
        {
            '$match':
                {
                    'rejectReason': None,
                    'price': {'$ne': None}
                }
        },
        {
            '$lookup': {
                'from': "merchants",
                'localField': "merchantCode",
                'foreignField': "code",
                'as': "res"
            }
        }, {
            '$unwind': {
                'path': "$res",
            }
        },
        {
            '$project':
                {
                    'warehouseCode': 1,
                    'serial': 1,
                    'spu': 1,
                    'sku': 1,
                    'old_shelf': 1,
                    'new_shelf': 1,
                    'old_uni': 1,
                    'new_uni': 1,
                    'originalFailReason': 1,
                    'price': 1,
                    'status': 1,
                    'finishedAt': 1,
                    'merchantCode': "$res.name",
                    'rejectReason': 1,
                }
        },
    ]
    for i in alpha.repairorders.aggregate(pipeline=pipeline):
        if i['old_uni'] == i['new_uni']:
            pass
        elif 'originalFailReason' in i and 'new_shelf' in i:
            li.append([i['serial'], i['warehouseCode'], spuId_to_articleNumber('alpha', i['spu']),
                       skuId_to_size('alpha', i['sku']), i['old_shelf'], i['new_shelf'], i['old_uni'], i['new_uni'],
                       i['originalFailReason'], i['price'], i['status'], timestamp_switch_date(i['finishedAt']),
                       i['merchantCode']])
        elif 'originalFailReason' in i and 'new_shelf' not in i:
            li.append([i['serial'], i['warehouseCode'], spuId_to_articleNumber('alpha', i['spu']),
                       skuId_to_size('alpha', i['sku']), i['old_shelf'], '', i['old_uni'], i['new_uni'],
                       i['originalFailReason'], i['price'], i['status'], timestamp_switch_date(i['finishedAt']),
                       i['merchantCode']])
        elif 'originalFailReason' not in i and 'new_shelf' not in i:
            li.append([i['serial'], i['warehouseCode'], spuId_to_articleNumber('alpha', i['spu']),
                       skuId_to_size('alpha', i['sku']), i['old_shelf'], '', i['old_uni'], i['new_uni'],
                       '', i['price'], i['status'], timestamp_switch_date(i['finishedAt']),
                       i['merchantCode']])
        elif 'originalFailReason' not in i and 'new_shelf' in i:
            li.append([i['serial'], i['warehouseCode'], spuId_to_articleNumber('alpha', i['spu']),
                       skuId_to_size('alpha', i['sku']), i['old_shelf'], i['new_shelf'], i['old_uni'], i['new_uni'],
                       '', i['price'], i['status'], timestamp_switch_date(i['finishedAt']),
                       i['merchantCode']])
    write_xlsx(
        ['修复订单号', '仓库', '货号', '尺码', '原货架', '新货架', '原唯一码', '新唯一码', '瑕疵描述', '报价', '状态',
         '订单完成时间', '商家编号'], li, '6月修复订单')


def repair_export_all():
    """修复订单导出"""
    li = []
    pipeline = [
        {
            "$match":
                {
                    "createdAt": {
                        "$gt": 1690819200000
                    },
                    "status": {
                        "$ne": "cancelled"
                    }
                }
        },
        {
            "$unwind":
                {
                    "path": "$stockList"
                }
        },
        {
            "$match":

                {
                    "stockList.status": {
                        "$ne": "rejected"
                    },
                    "$expr": {
                        "$ne": [
                            "$stockList.originalUniqueCode",
                            "$stockList.uniqueCode"
                        ]
                    },
                    "stockList.price": {
                        "$ne": None
                    },
                    "stockList.originalFailReason": {
                        "$ne": None
                    }
                }
        },
        {
            "$project":
                {
                    "serial": 1,
                    "spu": "$stockList.spuId",
                    "sku": "$stockList.skuId",
                    "price": "$stockList.price",
                    "reason": "$stockList.originalFailReason",
                    "createdAt": 1,
                    "_id": 0
                }
        }
    ]
    for i in alpha.repairorders.aggregate(pipeline=pipeline):
        li.append([i['serial'], spuId_to_articleNumber('alpha', i['spu']), skuId_to_size('alpha', i['sku']), i['price'],
                   i['reason'], timestamp_switch_date(i['createdAt'])])
    write_xlsx(['修复订单号', '货号', '尺码', '报价', '瑕疵描述', '创建时间'], li, '8月至今修复订单')


if __name__ == '__main__':
    repair_export_all()
